/**
 * 
 */
package com.java.test.user.mapper;

import com.java.test.user.dto.PlayAccountInfo;
import com.java.test.util.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Administrator
 *
 */
@Component
public class PlayAccountMapper {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int selectplayAccountCount(PlayAccountInfo info) {
		String sql = "select count(*) from member_play_account where 1=1 ";
		
		if (info.getFoundTypeId() != null && !"".equals(info.getFoundTypeId())) {
			sql += " and foundTypeId='" + info.getFoundTypeId() + "'";
		}

		if (info.getAccountType() != 0) {
			sql += " and account_type='" + info.getAccountType() + "'";
		}

		if (info.getSliceValue() != 0) {
			sql += " and slice_value ='" + info.getSliceValue() + "'";
		}

		if (info.getAreaId() != 0) {
			sql += " and area_id='" + info.getAreaId() + "'";
		}
		if (info.getGradeValue() != 0) {
			sql += " and grade_value='" + info.getGradeValue() + "'";
		}
		if (info.getRegisterTime() != null) {
			sql += " and TO_DAYS(register_time)= TO_DAYS('" + info.getRegisterTime() + "')";
		}
		if (info.getAccountName() != null && !"".equals(info.getAccountName())) {
			sql += " and account like '%" + info.getAccountName() + "%'";
		}

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i == null ? null : i;
	}

	public List<PlayAccountInfo> selectplayAccountList(PlayAccountInfo info, PageInfo<PlayAccountInfo> page) {
		String sql = "select a.*,b.name as foundTypeName from member_play_account a left join sys_funds_type b on a.foundTypeId=b.name where 1=1 ";

		if (info.getFoundTypeId() != null && !"".equals(info.getFoundTypeId())) {
			sql += " and a.foundTypeId='" + info.getFoundTypeId() + "'";
		}

		if (info.getAccountType() != 0) {
			sql += " and a.account_type='" + info.getAccountType() + "'";
		}

		if (info.getSliceValue() != 0) {
			sql += " and a.slice_value ='" + info.getSliceValue() + "'";
		}

		if (info.getAreaId() != 0) {
			sql += " and a.area_id='" + info.getAreaId() + "'";
		}
		if (info.getGradeValue() != 0) {
			sql += " and a.grade_value='" + info.getGradeValue() + "'";
		}
		if (info.getRegisterTime() != null) {
			sql += " and TO_DAYS(a.register_time)= TO_DAYS('" + info.getRegisterTime() + "')";
		}
		if (info.getAccountName() != null && !"".equals(info.getAccountName())) {
			sql += " and a.account like '%" + info.getAccountName() + "%'";
		}
		int start = (page.getCurrentPage() - 1) * page.getPageCount();
		int limit = page.getPageCount();
		sql += " order by a.register_time desc limit ?,?";
		List<PlayAccountInfo> list = jdbcTemplate.query(sql, new PlayAccountInfo(), start, limit);
		return list;
	}

	public List<String> selectpushUserList(String sql) {

		List<String> list = jdbcTemplate.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int i) throws SQLException {
				return rs.getString("accountName");
			}
		});
		return list;
	}

	public int updatemembergradeslice(PlayAccountInfo info) {
		String sql = "update member_play_account set slice_value=?,slice_name=?,grade_value=?,grade_name=?,recharge_amount=(recharge_amount+?) where accountName=?";
		int i = jdbcTemplate.update(sql, info.getSliceValue(), info.getSliceName(), info.getGradeValue(),
				info.getGradeName(), info.getRecharge_amount(), info.getAccountName());
		return i;
	}

	public int updatePlayAccountNotUse(PlayAccountInfo info) {
		String sql = "update member_play_account set account_status=0 where id=?";
		int i = jdbcTemplate.update(sql, info.getId());
		return i;
	}

	public int updatePlayAccountUse(PlayAccountInfo info) {
		String sql = "update member_play_account set account_status=1 where id=?";
		int i = jdbcTemplate.update(sql, info.getId());
		return i;
	}

	public int updatePlayAccountNotUsepl(final int[] ids) {
		String sql = "update member_play_account set account_status=0 where id=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, ids[i]);
			}

			public int getBatchSize() {
				return ids.length;
			}
		});
		return 1;
	}

	public int updatePlayAccountUsepl(final int[] ids) {
		String sql = "update member_play_account set account_status=1 where id=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, ids[i]);
			}

			public int getBatchSize() {
				return ids.length;
			}
		});
		return 1;
	}

	public int updatePlayCurentAmout(String account, double amount, int type) {
		// 添加
		String sql = "update member_play_account set cur_amount=A where account=?";
		if (type == 1) {
			sql = sql.replace("A", "(cur_amount+" + amount + ")");
		} else {
			sql = sql.replace("A", "(cur_amount-" + amount + ")");
		}
		int i = jdbcTemplate.update(sql, account);
		return i;
	}

	public PlayAccountInfo selectplayaccountbyId(int id) {

		String sql = "select * from member_play_account where id=?";

		PlayAccountInfo info = jdbcTemplate.queryForObject(sql, new PlayAccountInfo(), id);

		return info;

	}

	/**
	 * 查询当前会员条数
	 * 
	 * @author
	 * @date 2020/7/23 0023 10:25
	 * @return int
	 */
	public int getMemberListSize() {
		String sql = "select count(*) from member_play_account ";

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i == null ? 0 : i;
	}

	/**
	 * 查询新会员
	 * 
	 * @author
	 * @date 2020/7/23 0023 10:25
	 * @return java.util.List<com.java.test.user.dto.MemberInfo>
	 */
	public List<PlayAccountInfo> getMemberListNow(String startTime) {
		String sql = "SELECT * FROM member_play_account t WHERE t.register_time  >= str_to_date('" + startTime
				+ "', '%Y-%m-%d')";
		List<PlayAccountInfo> list = jdbcTemplate.query(sql, new PlayAccountInfo());
		return list;
	}

	/**
	 * 查询老会员
	 * 
	 * @author
	 * @date 2020/7/23 0023 10:25
	 * @return java.util.List<com.java.test.user.dto.MemberInfo>
	 */
	public List<PlayAccountInfo> getMemberListOld(String startTime) {
		String sql = "SELECT * FROM member_play_account t WHERE t.register_time  < str_to_date('" + startTime
				+ "', '%Y-%m-%d')";
		List<PlayAccountInfo> list = jdbcTemplate.query(sql, new PlayAccountInfo());
		return list;
	}

	/**
	 * 查询老会员
	 * 
	 * @author
	 * @date 2020/7/23 0023 10:25
	 * @return java.util.List<com.java.test.user.dto.MemberInfo>
	 */
	public List<PlayAccountInfo> getMemberListByAccountName(String accountName) {
		String sql = "SELECT * FROM member_play_account t WHERE t.accountName = ?";
		List<PlayAccountInfo> list = jdbcTemplate.query(sql, new PlayAccountInfo(), accountName);
		return list;
	}

	/**
	 * 修改金额
	 * 
	 * @param amount  值
	 * @param account 用户
	 * @author
	 * @date 2020/8/6 0006 10:57
	 * @return int
	 */
	public int updateFrozenAmountByAccount(Double[] amount, String account) {
		String sql = "update member_play_account t set t.cur_amount = cur_amount - ? , t.frozen_amount = frozen_amount + ? where t.account = ?";
		String[] params = new String[amount.length + 1];
		for (int i = 0; i < amount.length; i++) {
			params[i] = Double.toString(amount[i]);
		}
		params[amount.length] = account;

		int i = jdbcTemplate.update(sql, params);
		return i;
	}

	public String getUniqueNumber(String gameAccount) {
		String sql = "select * from member_play_account where account=?";

		List<PlayAccountInfo> list = jdbcTemplate.query(sql, new PlayAccountInfo(), gameAccount);

		if (list == null || list.size() <= 0) {
			return null;
		}
		return list.get(0).getAccountName();

	}

	/**
	 * 更新用户金额 存款成功
	 * 
	 * @param gameAccount
	 * @param balance     当前余额
	 * @param frozen      冻结金额
	 * @return
	 */
	public int updatedepositsuccessuserAmount(List<PlayAccountInfo> info) {

		String sql = "update member_play_account set cur_amount=(cur_amount+?),cunkuan=? where account=?";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return info.size();
				// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
			}

			@SuppressWarnings("static-access")
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setDouble(1, info.get(i).getCurAmount());
				ps.setDouble(2, info.get(i).getCunkuan());
				ps.setString(3, info.get(i).getAccount());

			}
		});
		return 1;
	}

	/**
	 * 提款失败
	 * 
	 * @param gameAccount
	 * @param balance     当前余额
	 * @param frozen      冻结金额
	 * @return
	 */
	public int updaterefectfailduserAmount(List<String> list) {

		String sql = "update member_play_account set cur_amount=(cur_amount+frozen_amount),frozen_amount=0 where account=?";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return list.size();
				// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
			}

			@SuppressWarnings("static-access")
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, list.get(i));
			}
		});
		return 1;
	}

	/**
	 * 提款成功
	 * 
	 * @param gameAccount
	 * @param balance     当前余额
	 * @param frozen      冻结金额
	 * @return
	 */
	public int updaterefectsuccessuserAmount(double tikuan, String account) {

		String sql = "update member_play_account set frozen_amount=(frozen_amount-?),tikuan=? where account=?";

		return jdbcTemplate.update(sql, tikuan, tikuan, account);
	}

	/**
	 * @param info
	 * @return
	 */
	public List<PlayAccountInfo> exportExcel(PlayAccountInfo info) {
		String sql = "select * from member_play_account where 1=1 ";

		if (info.getAccountType() != 0) {
			sql += " and account_type='" + info.getAccountType() + "'";
		}

		if (info.getSliceValue() != 0) {
			sql += " and slice_value ='" + info.getSliceValue() + "'";
		}

		if (info.getAreaId() != 0) {
			sql += " and area_id='" + info.getAreaId() + "'";
		}
		if (info.getGradeValue() != 0) {
			sql += " and grade_value='" + info.getGradeValue() + "'";
		}
		if (info.getAccountName() != null && !"".equals(info.getAccountName())) {
			sql += " and account like '%" + info.getAccountName() + "%'";
		}
		sql += " order by register_time desc";
		List<PlayAccountInfo> list = jdbcTemplate.query(sql, new PlayAccountInfo());
		return list;
	}

	/**
	 * @param info
	 * @return
	 */
	public int updateplayremarks(PlayAccountInfo info) {

		String sql = "update member_play_account set player_remarks=? where id=?";

		return jdbcTemplate.update(sql, info.getPlayerRemarks(), info.getId());
	}

}
